1

I have, what I believe is a very simple script, that is giving me some weird problems.

Param (
	[DateTime]$DateTime = (Get-Date),
	[string]$MDSIFServer = "WTCVHOMDSIF001"
)
$StatsByAccount = Get-ChildItem "\\$($MDSIFServer)\C$\Program Files\TV2Dtry" -Recurse -Filter "*statsbyaccount*.csv" |	Where-Object { $_.LastWriteTime -gt (Get-Date(Get-Date).AddMinutes(-20)) }
$StatsByService = Get-ChildItem "\\$($MDSIFServer)\C$\Program Files\TV2Dtry" -Recurse -Filter "*statsbyservice*.csv" |	Where-Object { $_.LastWriteTime -gt (Get-Date(Get-Date).AddMinutes(-20)) }
if (Test-Path $($StatsByAccount.Fullname))
{
	$AccountsCSV = Import-Csv $StatsByAccount.Fullname
}
else
{
	Write-Host "Accounts CSV file missing, make sure it's still there."	
}
if (Test-Path $($StatsByAccount.Fullname))
{
	$ServicesCSV = Import-Csv $StatsByService.Fullname
}
else
{
	Write-Host "Services CSV file missing, make sure it's still there."	
}
$server = "192.168.103.121" #IP address or server name
$database = "IPTVReports" #Database name to connect to 
$username = "UserHere" #Local username for database
$password = "PassHere" #Local Password for database
$connString = "Server=$server;Database=$database;user id=$username;password=$password;"
$dataAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
$Query = @"
BULK INSERT [IPTVReports].[dbo].[IPTV.DServerByAccount] FROM '$($StatsByAccount.Fullname)'
WITH (FIRSTROW = 2, FIELDTERMINATOR = ',', ROWTERMINATOR = '\n')
"@
$dataAdapter.SelectCommand = new-object System.Data.SqlClient.SqlCommand ($query,$connString)
$commandBuilder = new-object System.Data.SqlClient.SqlCommandBuilder $dataAdapter
$dt = New-Object System.Data.DataTable #This will store the results of the query. You can pull the results out by simply calling $dt
[void]$dataAdapter.fill($dt)
### Display the results of the query in the powershell window
#$dt | Out-GridView

Produces the error:

PS C:\Deploy\Import-DServerRetryReport> .\Import-DServerRetryReport2.ps1
Exception calling "Fill" with "1" argument(s): "Cannot bulk load because the file "\\WTCVHOMDSIF001\C$\Program Files\TV2Dt
ry\DTry_AllClusters_20140211T135009_D0001\DTry_AllClusters_StatsByAccount_20140211T135009_D0001.csv" could not be opene
d. Operating system error code 53(The network path was not found.)."
At C:\Deploy\Import-DServerRetryReport\Import-DServerRetryReport2.ps1:128 char:24
+ [void]$dataAdapter.fill <<<< ($dt)
    + CategoryInfo          : NotSpecified: (:) [], MethodInvocationException
    + FullyQualifiedErrorId : DotNetMethodException

I have also tried copying the remote file locally then doing the bulk import from the local file but it give essentially the same error. The file is not in use. I can open it fine, and the UNC path is accessible through windows explorer.

Any ideas what’s throwing the “could not be opened error” ?

4 Spice ups
2

Look at your second Test-Path, you’re testing for StatsByAccount again when it should be StatsByService…

3

Thanks for the catch Martin, But as for my problem that does not help it. It my test so far i am only trying to bulk import the Accounts CSV not the Services CSV.

4

My guess is it’s a permissions thing. You’re sending the command off to the SQL server and it’s trying to come back to your PC and pull the CSV and may not have permissions. Just as a test I’d share out the last folder and assign Everyone:Manage permissions then pass on the new \server\share UNC and see if that does anything. At the very least we can eliminate permissions as the problem!

5

OMG…that never even dawned on me. It’s because I’m going across 2 domains. But I’m not sure if I can specify in the SQL portion what credentials to use for connecting to the other domain to grab the csv file.

6

In that case prolly better off copying the CSV over to the SQL server, at at the very least to a server on the same domain.

7

That’s exactly what I was thinking about. It’s either that or, create a DB instance on my current domain to write this data too, then create an SSRS report. The SSRS server can access all DB’s in both domains. But neither domain can talk to each other, for security purposes.

1 Spice up